package com.bootdo.jiashan.service;

import com.bootdo.common.utils.MoneyConvert;
import com.bootdo.common.utils.StringUtils;
import com.bootdo.jiashan.dao.SupplierDao;
import com.bootdo.jiashan.domain.SupplyProductDO;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.InputStream;
import java.util.*;

/**
 * @Author: hje
 * @Description:
 * @Date: 2019-05-26 01:26
 */
@Service
public class ImportService {

    static byte[] black = new byte[]{0,0,0};
    static byte[] red = new byte[]{-1,-64,0,0};
    static byte[] lan = new byte[]{-1,0,-80,-16};
    static String factoryName = null;
    static Integer factoryId = null;
    static String name = null;
    static Row firstRow = null;
    static Row secondRow = null;

    /**
     * 处理上传的文件
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public List getBankListByExcel(InputStream in, String fileName) throws Exception {
        List list = new ArrayList();
//        //创建Excel工作薄
//        Workbook work = this.getWorkbook(in, fileName);
//        if (null == work) {
//            throw new Exception("创建Excel工作薄为空！");
//        }
//        Sheet sheet = null;
//        Row row = null;
//        Cell cell = null;
//
//        for (int i = 0; i < work.getNumberOfSheets(); i++) {
//            sheet = work.getSheetAt(i);
//            if (sheet == null) {
//                continue;
//            }
//
//            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
//                row = sheet.getRow(j);
//                if (row == null || row.getFirstCellNum() == j) {
//                    continue;
//                }
//
//                List li = new ArrayList();
//                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
//                    cell = row.getCell(y);
//                    li.add(cell);
////                    Arrays.equals(red,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())
//                }
//                list.add(li);
//            }
//        }
//        work.close();
        return list;
    }

    @Transactional(rollbackFor = Exception.class)
    public void aa(InputStream in, String fileName,SupplierDao supplierDao,SupplyProductService supplyProductService) throws Exception {
        Map colorErr = new HashMap();
        Map cellErr = new HashMap();
        //创建Excel工作薄
        Workbook work = this.getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = work.getSheetAt(0);
        firstRow = sheet.getRow(0);
//        for (int y = 2; y < firstRow.getLastCellNum(); y++) {
//            Cell cell = firstRow.getCell(y);
//            if(StringUtils.isBlank(cell.getStringCellValue())){
//                continue;
//            }
//            SupplierDO supplierDO = new SupplierDO();
//            supplierDO.setName(cell.getStringCellValue());
//            RejectUtil.nullToEmptyStrOr0(supplierDO);
//            supplierDao.save(supplierDO);
//        }
        secondRow = sheet.getRow(1);
        Row row = null;
        Cell cell = null;
        for (int j = 2; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j) {
                continue;
            }
            String proName = row.getCell(0).toString();
            if(StringUtils.isNotBlank(proName)){
                name = proName;
            }
            Cell normCell = row.getCell(1);
            normCell.setCellType(XSSFCell.CELL_TYPE_STRING);
            String norm = normCell.getStringCellValue();
            for (int y = 2; y < row.getLastCellNum(); y++) {
                try {
                    //厂名
                    String facName = firstRow.getCell(y).toString();
                    if(StringUtils.isNotBlank(facName)){
                        factoryName = facName;
                        Map<String, Object> map = new HashMap<>();
                        map.put("nameOrContact",factoryName);
                        factoryId = supplierDao.list(map).get(0).getId();
                    }
                    cell = row.getCell(y);
                    if(cell == null || StringUtils.isBlank(cell.toString())){// 没有价格说明没有此产品
                        continue;
                    }
                    String cellValue = cell.toString();
                    if(cellValue.contains("/")){
                        for (String price : cellValue.split("/")) {
                            SupplyProductDO supplyProductDO = new SupplyProductDO();
                            // 重量、价格
                            supplyProductDO.setWeight(MoneyConvert.DollarToCentForLong(price));
                            supplyProductDO.setUnitPrice(MoneyConvert.DollarToCentForLong(price));
                            supplyProductDO.setRealPrice(MoneyConvert.DollarToCentForLong(price));
                            // 厂
                            supplyProductDO.setSupplyId(factoryId);

                            // 产品名称
                            supplyProductDO.setName(name);
                            // 产品品规

                            supplyProductDO.setNorm(norm);

                            //品牌
                            supplyProductDO.setBrand(secondRow.getCell(y).toString());
                            //标准
                            if(((XSSFCell)cell).getCellStyle().getFont().getXSSFColor() == null||Arrays.equals(black,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())){
                                // 中标
                                supplyProductDO.setStandard(2);
                            }else if(Arrays.equals(red,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())){
                                // 非标
                                supplyProductDO.setStandard(3);
                            }else if(Arrays.equals(lan,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())){
                                // 国标
                                supplyProductDO.setStandard(1);
                            }
                            supplyProductService.save(supplyProductDO);
                        }
                    }else{
                        SupplyProductDO supplyProductDO = new SupplyProductDO();
                        // 重量、价格
                        supplyProductDO.setWeight(MoneyConvert.DollarToCentForLong(cellValue));
                        supplyProductDO.setUnitPrice(MoneyConvert.DollarToCentForLong(cellValue));
                        supplyProductDO.setRealPrice(MoneyConvert.DollarToCentForLong(cellValue));

                        //厂
                        supplyProductDO.setSupplyId(factoryId);

                        // 产品名称
                        supplyProductDO.setName(name);
                        // 产品品规
                        supplyProductDO.setNorm(row.getCell(1).toString());

                        //品牌
                        supplyProductDO.setBrand(secondRow.getCell(y).toString());
                        try {
                            //标准
                            if(((XSSFCell)cell).getCellStyle().getFont().getXSSFColor() == null||Arrays.equals(black,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())){
                                // 中标
                                supplyProductDO.setStandard(2);
                            }else if(Arrays.equals(red,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())){
                                // 非标
                                supplyProductDO.setStandard(3);
                            }else if(Arrays.equals(lan,((XSSFCell)cell).getCellStyle().getFont().getXSSFColor().getCTColor().getRgb())){
                                // 国标
                                supplyProductDO.setStandard(1);
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                            System.out.println("row:"+j+",cell:"+y);
                            colorErr.put(j,y);
                            continue;
                        }
                        supplyProductService.save(supplyProductDO);
                    }
                    

                } catch (Exception e) {
                    System.out.println("row:"+j+",cell:"+y);
                    cellErr.put(j,y);
                    e.printStackTrace();
                    continue;
                }
            }
        }
        System.out.println(colorErr);
        System.out.println(cellErr);
    }

    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }
}
